#include "Avalon/ServiceLocator/MySqlServiceLocatorDataStore.hpp"
#include <boost/lexical_cast.hpp>
#include <mysql++/mysql++.h>
#include <mysql++/ssqls.h>
#ifdef CreateDirectory
  #undef CreateDirectory
#endif
#include "Avalon/IO/ConnectException.hpp"
#include "Avalon/ServiceLocator/Account.hpp"
#include "Avalon/ServiceLocator/Directory.hpp"

using namespace Avalon;
using namespace Avalon::IO;
using namespace Avalon::Network;
using namespace Avalon::ServiceLocator;
using namespace Avalon::Threading;
using namespace boost;
using namespace boost::posix_time;
using namespace mysqlpp;
using namespace std;

namespace {
  namespace SqlInsert {
    sql_create_1(settings, 1, 0,
      mysqlpp::sql_int_unsigned, next_entry_id);

    sql_create_2(directories, 2, 0,
      mysqlpp::sql_int_unsigned, id,
      mysqlpp::sql_varchar, name);

    sql_create_2(directory_entries, 2, 0,
      mysqlpp::sql_int_unsigned, entry,
      mysqlpp::sql_int_unsigned, directory);

    sql_create_3(accounts, 1, 3,
      mysqlpp::sql_int_unsigned, id,
      mysqlpp::sql_varchar, name,
      mysqlpp::sql_varchar, password);

    sql_create_3(permissions, 3, 0,
      mysqlpp::sql_int_unsigned, directory,
      mysqlpp::sql_int_unsigned, account,
      mysqlpp::sql_int_unsigned, permission);

    sql_create_1(unavailable_names, 1, 0,
      mysqlpp::sql_varchar, name);
  }

  sql_create_1(settings, 1, 0,
    mysqlpp::sql_int_unsigned, next_entry_id);

  sql_create_2(directories, 2, 0,
    mysqlpp::sql_int_unsigned, id,
    mysqlpp::sql_varchar, name);

  sql_create_2(directory_entries, 2, 0,
    mysqlpp::sql_int_unsigned, entry,
    mysqlpp::sql_int_unsigned, directory);

  sql_create_3(accounts, 1, 3,
    mysqlpp::sql_int_unsigned, id,
    mysqlpp::sql_varchar, name,
    mysqlpp::sql_varchar, password);

  sql_create_3(permissions, 3, 0,
    mysqlpp::sql_int_unsigned, directory,
    mysqlpp::sql_int_unsigned, account,
    mysqlpp::sql_int_unsigned, permission);

  sql_create_1(unavailable_names, 1, 0,
    mysqlpp::sql_varchar, name);

  bool TableExists(mysqlpp::Connection* databaseConnection,
      const string& schema, const char* table) {
    Query query = databaseConnection->query();
    query << "SHOW TABLES IN " << schema << " LIKE " << quote << table;
    StoreQueryResult result = query.store();
    return !result.empty();
  }

  bool LoadSettings(mysqlpp::Connection* databaseConnection,
      unsigned int* nextEntryId) {
    Query query = databaseConnection->query();
    query << "SELECT * FROM settings";
    StoreQueryResult result = query.store();
    if(!result) {
      return false;
    }
    settings row = result.front();
    *nextEntryId = row.next_entry_id;
    return true;
  }

  bool LoadSettingsTable(mysqlpp::Connection* databaseConnection,
      const string& schema) {
    if(TableExists(databaseConnection, schema, "settings")) {
      return true;
    }
    Query query = databaseConnection->query();
    query << "CREATE TABLE settings ("
      "next_entry_id INTEGER UNSIGNED NOT NULL)";
    if(!query.execute()) {
      return false;
    }
    query.reset();
    SqlInsert::settings settingsRow(1);
    query.insert(settingsRow);
    return query.execute();
  }

  bool LoadDirectoriesTable(mysqlpp::Connection* databaseConnection,
      const string& schema) {
    if(TableExists(databaseConnection, schema, "directories")) {
      return true;
    }
    Query query = databaseConnection->query();
    query << "CREATE TABLE directories ("
      "id INTEGER UNSIGNED PRIMARY KEY NOT NULL,"
      "name VARCHAR(100) NOT NULL)";
    return query.execute();
  }

  bool LoadDirectoryEntriesTable(mysqlpp::Connection* databaseConnection,
      const string& schema) {
    if(TableExists(databaseConnection, schema, "directory_entries")) {
      return true;
    }
    Query query = databaseConnection->query();
    query << "CREATE TABLE directory_entries ("
      "entry INTEGER UNSIGNED PRIMARY KEY NOT NULL,"
      "directory INTEGER UNSIGNED NOT NULL)";
    return query.execute();
  }

  bool LoadAccountsTable(mysqlpp::Connection* databaseConnection,
      const string& schema) {
    if(TableExists(databaseConnection, schema, "accounts")) {
      return true;
    }
    Query query = databaseConnection->query();
    query << "CREATE TABLE accounts ("
      "id INTEGER UNSIGNED PRIMARY KEY NOT NULL,"
      "name VARCHAR(100) NOT NULL,"
      "password VARCHAR(100) NOT NULL)";
    return query.execute();
  }

  bool LoadPermissionsTable(mysqlpp::Connection* databaseConnection,
      const string& schema) {
    if(TableExists(databaseConnection, schema, "permissions")) {
      return true;
    }
    Query query = databaseConnection->query();
    query << "CREATE TABLE permissions ("
      "directory INTEGER UNSIGNED NOT NULL,"
      "account INTEGER UNSIGNED NOT NULL,"
      "permission INTEGER UNSIGNED NOT NULL)";
    return query.execute();
  }

  bool LoadUnavailableNamesTable(mysqlpp::Connection* databaseConnection,
      const string& schema) {
    if(TableExists(databaseConnection, schema, "unavailable_names")) {
      return true;
    }
    Query query = databaseConnection->query();
    query << "CREATE TABLE unavailable_names ("
      "name VARCHAR(100) NOT NULL)";
    return query.execute();
  }

  bool LoadTables(mysqlpp::Connection* databaseConnection,
      const string& schema) {
    if(!LoadSettingsTable(databaseConnection, schema)) {
      return false;
    }
    if(!LoadDirectoryEntriesTable(databaseConnection, schema)) {
      return false;
    }
    if(!LoadDirectoriesTable(databaseConnection, schema)) {
      return false;
    }
    if(!LoadAccountsTable(databaseConnection, schema)) {
      return false;
    }
    if(!LoadPermissionsTable(databaseConnection, schema)) {
      return false;
    }
    if(!LoadUnavailableNamesTable(databaseConnection, schema)) {
      return false;
    }
    return true;
  }
}

MySqlServiceLocatorDataStore::MySqlServiceLocatorDataStore(
    const IpAddress& address, const string& schema, const string& username,
    const string& password)
    : m_address(address),
      m_schema(schema),
      m_username(username),
      m_password(password),
      m_databaseConnection(new mysqlpp::Connection(false)),
      m_isOpen(false) {}

MySqlServiceLocatorDataStore::~MySqlServiceLocatorDataStore() {
  Close();
}

int MySqlServiceLocatorDataStore::LoadAccountCount() {
  Query query = m_databaseConnection->query();
  query << "SELECT COUNT(id) FROM accounts";
  StoreQueryResult result = query.store();
  if(!result) {
    BOOST_THROW_EXCEPTION(IOException(query.error()));
  }
  return result[0][0].conv<int>(0);
}

DirectoryEntry::Tag MySqlServiceLocatorDataStore::LoadTag(unsigned int id) {
  Query query = m_databaseConnection->query();
  query <<
    "SELECT " << DirectoryEntry::Type::DIRECTORY <<
    "  AS type, id, name FROM directories WHERE directories.id = " << id <<
    " UNION "
    "SELECT " << DirectoryEntry::Type::ACCOUNT <<
    "  AS type, id, name FROM accounts WHERE accounts.id = " << id;
  StoreQueryResult result = query.store();
  if(!result) {
    BOOST_THROW_EXCEPTION(IOException(query.error()));
  }
  if(result.empty()) {
    return DirectoryEntry::Tag();
  }
  return DirectoryEntry::Tag(DirectoryEntry::Type::FromValue(
    static_cast<int>(result[0][0])), result[0][1], result[0][2].c_str());
}

DirectoryEntry::Tag MySqlServiceLocatorDataStore::LoadParentTag(
    unsigned int id) {
  Query query = m_databaseConnection->query();
  query << "SELECT * FROM directory_entries WHERE entry = " << id;
  StoreQueryResult result = query.store();
  if(!result) {
    BOOST_THROW_EXCEPTION(IOException(query.error()));
  }
  if(result.empty()) {
    return DirectoryEntry::Tag();
  }
  int parentId = result[0][1];
  if(parentId == -1 || parentId == id) {
    return DirectoryEntry::Tag(DirectoryEntry::Type::DIRECTORY, -1, "");
  }
  return LoadTag(parentId);
}

DirectoryEntry::Tag MySqlServiceLocatorDataStore::LoadAccountTag(
    const string& name) {
  Query query = m_databaseConnection->query();
  query << "SELECT id FROM accounts WHERE name = " << quote << name;
  StoreQueryResult result = query.store();
  if(!result) {
    BOOST_THROW_EXCEPTION(IOException(query.error()));
  }
  if(result.empty()) {
    return DirectoryEntry::Tag();
  }
  assert(result.size() == 1);
  return LoadTag(result[0][0]);
}

unsigned int MySqlServiceLocatorDataStore::CreateDirectory(const string& name,
    const DirectoryEntry::Tag& directory) {
  Query query = m_databaseConnection->query();
  Transaction transaction(*m_databaseConnection);

  // Get the directory id.
  unsigned int directoryId = LoadNextEntryId();

  // Create the directory.
  query.reset();
  SqlInsert::directories directoryRow(directoryId, name);
  query.insert(directoryRow);
  if(!query.execute()) {
    BOOST_THROW_EXCEPTION(IOException(query.error()));
  }

  // Add the directory to the parent directory.
  query.reset();
  SqlInsert::directory_entries directoryEntry(directoryId, directory.m_id);
  query.insert(directoryEntry);
  if(!query.execute()) {
    BOOST_THROW_EXCEPTION(IOException(query.error()));
  }
  transaction.commit();
  return directoryId;
}

void MySqlServiceLocatorDataStore::DeleteDirectory(unsigned int id) {
  Query query = m_databaseConnection->query();
  Transaction transaction(*m_databaseConnection);
  query << "DELETE FROM directories WHERE id = " << id;
  if(!query.execute()) {
    BOOST_THROW_EXCEPTION(IOException(query.error()));
  }
  query.reset();
  query << "DELETE FROM directory_entries WHERE entry = " << id;
  if(!query.execute()) {
    BOOST_THROW_EXCEPTION(IOException(query.error()));
  }
  transaction.commit();
}

void MySqlServiceLocatorDataStore::MoveEntry(unsigned int id,
    unsigned int directory) {
  Query query = m_databaseConnection->query();
  query << "UPDATE directory_entries SET directory = " << directory <<
    " WHERE entry = " << id;
  if(!query.execute()) {
    BOOST_THROW_EXCEPTION(IOException(query.error()));
  }
}

vector<DirectoryEntry::Tag> MySqlServiceLocatorDataStore::LoadDirectoryEntries(
    unsigned int id) {
  Query query = m_databaseConnection->query();
  vector<DirectoryEntry::Tag> entries;
  query <<
    "SELECT * FROM (SELECT " << DirectoryEntry::Type::DIRECTORY <<
    "  AS type, directories.id, directories.name FROM "
    "  directory_entries, directories WHERE directory_entries.entry = "
    "  directories.id AND directory_entries.directory = " << id << ") AS "
    "  directory_tags UNION "
    "SELECT * FROM (SELECT " << DirectoryEntry::Type::ACCOUNT <<
    "  AS type, accounts.id, accounts.name FROM "
    "  directory_entries, accounts WHERE directory_entries.entry = "
    "  accounts.id AND directory_entries.directory = " << id << ") AS "
    "  account_tags";
  StoreQueryResult result = query.store();
  if(!result) {
    BOOST_THROW_EXCEPTION(IOException(query.error()));
  }
  for(StoreQueryResult::const_iterator i = result.begin();
      i != result.end(); ++i) {
    entries.push_back(DirectoryEntry::Tag(DirectoryEntry::Type::FromValue(
      static_cast<int>((*i)[0])), (*i)[1], (*i)[2].c_str()));
  }
  return entries;
}

unsigned int MySqlServiceLocatorDataStore::CreateAccount(const string& name,
    const DirectoryEntry::Tag& directory) {
  Query query = m_databaseConnection->query();
  Transaction transaction(*m_databaseConnection);

  // Create the account.
  unsigned int accountId = LoadNextEntryId();
  query.reset();
  SqlInsert::accounts accountRow(accountId, name, "");
  query.insert(accountRow);
  if(!query.execute()) {
    BOOST_THROW_EXCEPTION(IOException(query.error()));
  }

  // Add the account to the directory.
  query.reset();
  SqlInsert::directory_entries directoryEntry(accountId, directory.m_id);
  query.insert(directoryEntry);
  if(!query.execute()) {
    BOOST_THROW_EXCEPTION(IOException(query.error()));
  }
  transaction.commit();
  return accountId;
}

void MySqlServiceLocatorDataStore::DeleteAccount(unsigned int id) {
  Query query = m_databaseConnection->query();
  Transaction transaction(*m_databaseConnection);
  query << "DELETE FROM accounts WHERE id = " << id;
  if(!query.execute()) {
    BOOST_THROW_EXCEPTION(IOException(query.error()));
  }
  query.reset();
  query << "DELETE FROM directory_entries WHERE entry = " << id;
  if(!query.execute()) {
    BOOST_THROW_EXCEPTION(IOException(query.error()));
  }
  transaction.commit();
}

string MySqlServiceLocatorDataStore::LoadPassword(unsigned int account) {
  Query query = m_databaseConnection->query();
  query << "SELECT password FROM accounts WHERE id = " << account;
  StoreQueryResult result = query.store();
  if(!result) {
    BOOST_THROW_EXCEPTION(IOException(query.error()));
  }
  if(result.empty()) {
    BOOST_THROW_EXCEPTION(IOException("Account does not exist."));
  }
  assert(result.size() == 1);
  return result[0][0].c_str();
}

void MySqlServiceLocatorDataStore::SetPassword(unsigned int account,
    const string& password) {
  Query query = m_databaseConnection->query();
  query << "UPDATE accounts SET password = " << quote << password <<
    " WHERE id = " << account;
  if(!query.execute()) {
    BOOST_THROW_EXCEPTION(IOException(query.error()));
  }
}

map<unsigned int, Directory::Permission>
    MySqlServiceLocatorDataStore::LoadPermissions(unsigned int id) {
  Query query = m_databaseConnection->query();
  query << "SELECT * FROM permissions WHERE directory = " << id;
  vector<permissions> permissionsResult;
  query.storein(permissionsResult);
  map<unsigned int, Directory::Permission> permissionMap;
  for(vector<permissions>::const_iterator i = permissionsResult.begin();
      i != permissionsResult.end(); ++i) {
    permissionMap.insert(make_pair(i->account, Directory::Permission(
      i->permission)));
  }
  return permissionMap;
}

Directory::Permission MySqlServiceLocatorDataStore::LoadAccountPermissions(
    unsigned int account, unsigned int directory) {
  Query query = m_databaseConnection->query();
  query << "SELECT permission FROM permissions WHERE account = " << account <<
    " AND directory = " << directory;
  StoreQueryResult result = query.store();
  if(!result) {
    BOOST_THROW_EXCEPTION(IOException(query.error()));
  }
  if(result.empty()) {
    return Directory::Permission::NONE;
  }
  return Directory::Permission(result[0][0]);
}

void MySqlServiceLocatorDataStore::SetPermissions(unsigned int account,
    unsigned int directory, Directory::Permission permissions) {
  Query query = m_databaseConnection->query();
  query << "SELECT * FROM permissions WHERE account = " << account <<
    " AND directory = " << directory;
  StoreQueryResult result = query.store();
  if(!result) {
    BOOST_THROW_EXCEPTION(IOException(query.error()));
  }
  query.reset();
  if(result.empty()) {
    SqlInsert::permissions permissionRow(directory, account,
      permissions.GetValue());
    query.insert(permissionRow);
  } else {
    query << "UPDATE permissions SET permissions = " <<
      permissions.GetValue() << " WHERE account = " << account <<
      " AND directory = " << directory;
  }
  if(!query.execute()) {
    BOOST_THROW_EXCEPTION(IOException(query.error()));
  }
}

vector<string> MySqlServiceLocatorDataStore::GetUnavailableNames() {
  Query query = m_databaseConnection->query();
  query << "SELECT * FROM unavailable_names";
  StoreQueryResult result = query.store();
  if(!result) {
    BOOST_THROW_EXCEPTION(IOException(query.error()));
  }
  vector<string> unavailableNames;
  for(StoreQueryResult::const_iterator i = result.begin();
      i != result.end(); ++i) {
    unavailableNames.push_back(i->at(0).conv<string>(""));
  }
  return unavailableNames;
}

bool MySqlServiceLocatorDataStore::IsNameAvailable(const string& name) {
  Query query = m_databaseConnection->query();
  query << "SELECT * FROM unavailable_names WHERE name = " << quote << name;
  StoreQueryResult result = query.store();
  if(!result) {
    BOOST_THROW_EXCEPTION(IOException(query.error()));
  }
  return result.empty();
}

void MySqlServiceLocatorDataStore::AddUnavailableName(const string& name) {
  Query query = m_databaseConnection->query();
  SqlInsert::unavailable_names row(name);
  query.insert(row);
  if(!query.execute()) {
    BOOST_THROW_EXCEPTION(IOException(query.error()));
  }
}

void MySqlServiceLocatorDataStore::RemoveUnavailableName(const string& name) {
  Query query = m_databaseConnection->query();
  query << "DELETE FROM unavailable_names WHERE name = " << quote << name;
  if(!query.execute()) {
    BOOST_THROW_EXCEPTION(IOException(query.error()));
  }
}

void MySqlServiceLocatorDataStore::Open() {
  if(m_isOpen) {
    return;
  }

  // Connect to the database.
  bool connectionResult = m_databaseConnection->set_option(
    new mysqlpp::ReconnectOption(true));
  if(!connectionResult) {
    BOOST_THROW_EXCEPTION(IOException("Unable to set MySQL reconnect option."));
  }
  connectionResult = m_databaseConnection->connect(m_schema.c_str(),
    m_address.first.c_str(), m_username.c_str(), m_password.c_str(),
    m_address.second);
  if(connectionResult == false) {
    BOOST_THROW_EXCEPTION(ConnectException(
      "Unable to connect to MySQL database."));
  }

  // Load the tables and settings.
  if(!LoadTables(m_databaseConnection.get(), m_schema)) {
    BOOST_THROW_EXCEPTION(IOException("Unable to load database tables."));
  }
  if(!LoadSettings(m_databaseConnection.get(), &m_nextEntryId)) {
    BOOST_THROW_EXCEPTION(IOException("Unable to load database settings."));
  }
  m_isOpen = true;
}

bool MySqlServiceLocatorDataStore::IsOpen() const {
  return m_isOpen;
}

void MySqlServiceLocatorDataStore::Close() {
  if(!m_isOpen) {
    return;
  }
  m_databaseConnection->disconnect();
  m_isOpen = false;
  if(m_closedSlot == NULL) {
    return;
  }
  boost::shared_ptr<ClosedSlot> closedSlot = m_closedSlot;
  (*closedSlot)();
}

void MySqlServiceLocatorDataStore::SetClosedSlot(const ClosedSlot& slot,
    Out<bool> isOpen) {
  *isOpen = m_isOpen;
  m_closedSlot.reset(new ClosedSlot(slot));
}

unsigned int MySqlServiceLocatorDataStore::LoadNextEntryId() {
  unsigned int result = m_nextEntryId;
  Query query = m_databaseConnection->query();
  query << "UPDATE settings SET next_entry_id = " << (m_nextEntryId + 1);
  if(!query.execute()) {
    BOOST_THROW_EXCEPTION(IOException(query.error()));
  }
  ++m_nextEntryId;
  return result;
}
